SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [SOI].[FSIC_SOI_4Q13] AS
BEGIN
DECLARE @FundId VARCHAR(10) = 'FSIC'
 
 DECLARE @ExportId INT =
 (SELECT MAX(e.ExportId)
 FROM    FSFundModel_Valcomm.Reporting.Export e
 WHERE   e.FundId = @FundID )
 
 
 SELECT     ah.InstrClass
          , MAX(iss.IssuerName), ''
          , soi.GetFootNotes(MAX(a.assetIntrnlId), @ExportId) FootNotes , ''
          , MAX(iss.SubSector)                                Sector, ''
          , soi.GetFormattedRate(MAX(a.AssetIntrnlId), @ExportId ), ''
          
          , NULLIF(CAST(CAST(MAX(i.FLOOR)*100 AS DECIMAL(38,1)) AS VARCHAR(10)) +'%', '0.0%')                                                                                   AS FLOOR, ''
          , CAST(MONTH(MAX(i.MaturityDate)) AS VARCHAR(2)) + '/' + CAST(DAY(MAX(i.MaturityDate)) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(MAX(i.MaturityDate)) AS VARCHAR(4)), 2)    Maturity , '', ''
          ,
            --MAX(CONVERT(VARCHAR(8), i.MaturityDate,1)) Maturity,
            
            CASE
                       WHEN SUM(a.EndAdjAmtFor)> 0
                       THEN SUM(a.EndAdjAmtFor)
                       ELSE
						--CASE WHEN MAX(a.[AssetType]) <> 'Instrument' THEN SUM(a.TotalCredit) ELSE
						  COALESCE(NULLIF(SUM(a.EndAdjAmt) , 0.0), SUM(Oa.Units))
							--END 
            END AS 'Principal Amount', '' 
          , CASE
                       WHEN SUM(a.EndAdjAmtFor) > 0
                       THEN SUM(a.EndAdjAmtFor*a.AmortCostPctPar)
                       --ELSE CASE WHEN MAX(a.[AssetType]) <> 'Instrument' THEN SUM(a.TotalCredit) 
											 ELSE COALESCE(NULLIF(SUM(a.AmortCost), 0.0 ), SUM([oa].[AmortCost]))
											 --END 
            END AS 'Amortized Cost', ''
          , CASE
                        WHEN MAX([oa].[unFClass]) = 'Unfunded Positions' THEN  
												--SUM([a].TotalCredit)+SUM([a].Mktval) ELSE 
											 SUM([oa].[MktVal]) ELSE 	SUM([a].[MktVal] )  END  AS 'Fair Value', ''
          , MAX(i.BaseRateType)
          
					
				--	, [ah].UnfundedbyPfSort
		  --, a.FundCreditInstrumentId
		  , i.InstrumentId
		--	, [a].[AssetType]
				, [id].[SortOrder]
				, [oa].[unFClass]
				--, [a].[AssetType]
				--, MAX([a].[FundCreditInstrumentId])
 
 FROM       FSFundModel_Valcomm.Reporting.Asset a
            
            INNER JOIN FSFundModel_Valcomm.Reporting.Instrument i
            ON         i.ExportId     = a.ExportId
            AND        a.InstrumentId = i.InstrumentId
            INNER JOIN [ReportingScripts].[Valcomm].[AssetHelper](@ExportId) ah
						ON [ah].[AssetIntrnlId] = [a].[AssetIntrnlId]
			--LEFT JOIN FSFundModel_VALCOMM.Reporting.FundCreditInstrument fci 
			--ON fci.ExportId = a.ExportId 
			--AND fci.FundCreditInstrumentId = a.FundCreditInstrumentId 
            
            INNER JOIN FSFundModel_Valcomm.Reporting.Issuer iss
            ON         iss.ExportId = a.ExportId
            AND        i.IssuerId   = iss.IssuerId
            
            INNER JOIN FSFundModel_Valcomm.Reporting.InstrumentDefinition id
            ON         i.InstDefId = id.InstDefId
            AND        id.ExportId = a.ExportId
				OUTER APPLY(
								SELECT
  										 [i].[InstrName]
										 , MAX([a2].[PortfolioId]) PortfolioId
										 , MAX([a2].[InstrumentId]) InstrumentId
										, MAX([id].[InstrClass]) InstrClass
										, MAX([ah].[InstrClass]) unFClass
										 --, MAX([id].[InstrClass])
									, CASE WHEN  MAX([id].[InstrClass])  LIKE 'Equity%' THEN SUM([a2].[EndAdjAmt]) ELSE  SUM([a2].[TotalCredit]) END		 AS Units
										, CASE WHEN  MAX([id].[InstrClass])  LIKE 'Equity%' THEN SUM([a2].[MktVal]) ELSE  SUM([a2].[AvailCredit])+ SUM([a2].[MktVal]) END  AS [MktVal]
										--, SUM([a].[MktVal]) AS [MktVal]
										,CASE WHEN  MAX([id].[InstrClass])  LIKE 'Equity%' THEN SUM([a2].[AmortCost])  ELSE  SUM([a2].[TotalCredit]) END AS AmortCost

										



										FROM
											[FSFundModel_VALCOMM].[Reporting].[Asset] a2
											INNER JOIN [FSFundModel_VALCOMM].[Reporting].[Instrument] i ON [i].[ExportId] = [a2].[ExportId]
																																AND [i].[InstrumentId] = [a2].[InstrumentId]
										INNER JOIN  [ReportingScripts].[Valcomm].[AssetHelper](@ExportId) ah ON [ah].[AssetIntrnlId] = [a2].[AssetIntrnlId] 
										INNER JOIN  [FSFundModel_VALCOMM].[Reporting].[InstrumentDefinition] id ON [id].[ExportId] = [a2].[ExportId] AND [id].[InstDefId] = [i].[InstDefId]

										WHERE
	
											[a2].[ExportId] = @ExportId
											AND [a2].[IsActiveEndOfDay] =1
											AND [a2].[InstrumentId] = [a].[InstrumentId] AND [a2].[PortfolioId] = [a].[PortfolioId]
	
											AND (([a2].[AssetType] <> 'Instrument' OR [a2].[FundCreditInstrumentId] IS NOT NULL))

										GROUP BY [i].[InstrName]
				) oa 
 
 
 WHERE      a.ExportId = @ExportId
 AND ([ah].[InstrClass] = [oa].[unFClass] OR [oa].[unFClass] IS NULL )
 
 AND        (a.IsActiveEndOfDay =1)

 GROUP BY  

			
			i.InstrumentId
			-- iss.IssuerName
          , ah.InstrClass
					, [id].[SortOrder]
       --   , ah.UnfundedbyPfSort
				--	,[a].[AssetType]
		, [oa].[unFClass]
 
 ORDER BY [id].[SortOrder]    
				--ah.UnfundedbyPfSort
          --, iss.IssuerName
END

GO
